﻿#include "stdafx.h"
#include "StudentDB.h"
#include <string>

StudentDB::StudentDB()
{
}
Student StudentDB::selectById(const std::string &id)
{
	Student student;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from student where id="+id);

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string id = res->getString(1);
			string name = res->getString(2);
			string sex = res->getString(3);
			string className = res->getString(4);
			string deptName = res->getString(5);
			student.setId(id);
			student.setName(name);
			student.setSex(sex);
			student.setClassName(className);
			student.setDeptName(deptName);

		}
		delete res;
		delete stmt;
		delete con;
		return student;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}

}
void StudentDB::insert(Student &stu)
{
	try {
		cout << stu.getName() << endl;
		Connection *con;
		Statement *stmt;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		//string s = "insert into student values(" + stu.getId() + "," + stu.getName() + "," + stu.getSex() + "," + stu.getClassName() + "," + stu.getDeptName() + ")";
		//cout << s << endl;
		stmt->executeUpdate("insert into student values('" +stu.getId() +"','"+stu.getName() + "','" + stu.getSex() + "','" + stu.getClassName() + "','" + stu.getDeptName() +"')");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}
void StudentDB::deleteById(const std::string &id)
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("DELETE from student where id=" + id);
	
		delete stmt;
		delete con;
		
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

void StudentDB::update(Student &stu)
{
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		stmt->executeUpdate("update student set name='"+stu.getName() +"',sex='"+stu.getSex()+"',class_name='"+stu.getClassName()+"',dept_name='"+stu.getDeptName()+"' where id='" + stu.getId()+"'");
		delete stmt;
		delete con;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Student> StudentDB::selectAll(){
	list<Student> students;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from student");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string id = res->getString(1);
			string name = res->getString(2);
			string sex = res->getString(3);
			string className = res->getString(4);
			string deptName = res->getString(5);
			Student student;
			student.setId(id);
			student.setName(name);
			student.setSex(sex);
			student.setClassName(className);
			student.setDeptName(deptName);

			students.push_back(student);
		}
		delete res;
		delete stmt;
		delete con;
		return students;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

list<Student> StudentDB::selectByLike(const std::string &i) {
	list<Student> students;
	try {
		Connection *con;
		Statement *stmt;
		ResultSet *res;
		/* 创建连接 */
		con = getConnection();
		/* 连接 MySQL 数据库 tudentinfomanagementsys  */
		//con->setSchema("address");
		//cout << "数据库连接成功！" << endl;
		stmt = con->createStatement();
		stmt->execute("set names gbk");
		res = stmt->executeQuery("SELECT * from student where id like '%"+i+"%' or name like '%" + i + "%' or sex like '%" + i + "%' or class_name like '%" + i + "%' or dept_name like '%" + i + "%'");

		while (res->next()) {
			//cout << "\t... MySQL replies: ";
			/* 获取某列属性值通过列名 */
			//cout << res->getString("_message") << endl;
			//cout << "\t... MySQL says it again: ";
			/* 通过数字偏移量, 1 代表第一列 */
			string id = res->getString(1);
			string name = res->getString(2);
			string sex = res->getString(3);
			string className = res->getString(4);
			string deptName = res->getString(5);
			Student student;
			student.setId(id);
			student.setName(name);
			student.setSex(sex);
			student.setClassName(className);
			student.setDeptName(deptName);

			students.push_back(student);
		}
		delete res;
		delete stmt;
		delete con;
	
		return students;
	}
	catch (sql::SQLException &e) {
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}
}

StudentDB::~StudentDB()
{
}
